TD révision SQL

Langage de manipulation de données (LMD)

world
pagila
SQL
Published

December 19, 2025

WarningAvec solutions

Exercices de révision en SQL

La répartition des exercices n’est pas représentative de l’examen.

Solutions publiées le 4 janvier !

Exercice 1 : schéma world

NoteQuestion

Quels sont les pays dont plus de \(1.000.000\) d’habitants parlent une langue non officielle ? (72 lignes)

TipSolution
SELECT DISTINCT name_country
FROM country JOIN countrylanguage USING(countrycode)
WHERE (NOT isofficial) AND 
     (percentage / 100) * population_country >= 1000000 ;
NoteQuestion

Quels est le nombre de villes par region ?

TipSolution
SELECT co.region , COUNT(ci.id) AS nbr_city
FROM country co JOIN city ci USING(countrycode)
GROUP BY co.region;
NoteQuestion

Quelles sont les noms de villes qui désignent au moins deux villes distinctes ? (65 lignes)

TipSolution
SELECT DISTINCT ci1.name
FROM  city ci1 JOIN city ci2 
   ON ci1.name = ci2.name AND ci1.id <> ci2.id;
NoteQuestion

Quelles sont les langues parlées à la fois dans les continents nord-américain et sud-américain ? (10 lignes)

TipSolution
SELECT DISTINCT cl1.language
FROM  country c1 JOIN countrylanguage cl1 ON c1.countrycode = cl1.countrycode
      JOIN countrylanguage cl2 ON cl1.language = cl2.language 
      JOIN country c2 ON cl2.countrycode = c2.countrycode     
WHERE c1.continent ilike 'south am%' and c2.continent ilike 'north am%';
NoteQuestion

Quels sont les pays qui ont au moins \(3\) langues officielles ? (8 lignes)

TipSolution
SELECT c.name_country
FROM country c JOIN countrylanguage cl USING (countrycode)
WHERE cl.isofficial
GROUP BY c.name_country 
HAVING COUNT(*) > 2 ;
NoteQuestion

Quel est le pays qui a le plus de villes recensées dans la table city? Combien de villes ? (363 villes)

TipSolution
WITH country_city AS
   (SELECT co.countrycode, co.name_country , COUNT(ci.name) nbrcity
   FROM country co JOIN city ci USING (countrycode)
   GROUP BY co.countrycode, co.name_country)
SELECT countrycode, name_country, nbrcity
FROM country_city
WHERE nbrcity = 
   (SELECT MAX(nbrcity) 
    FROM country_city) ;
NoteQuestion

Quels sont les pays pour lesquels une langue non officielle est parlée par strictement plus de monde (dans le pays) qu’il n’y a d’habitants dans la capitale ? (118 lignes)

TipSolution
SELECT DISTINCT co.name_country
FROM country co 
   JOIN city ci ON ci.id = co.capital
   JOIN countrylanguage cl ON co.countrycode = cl.countrycode
WHERE NOT cl.isofficial
AND percentage / 100 * co.population_country > ci.population ;

Exercice 2 : schéma pagila

NoteQuestion

Quel est le prix moyen d’une location de DVD ?

TipSolution
SELECT AVG(amount) 
FROM payment ;
NoteQuestion

Quels sont les clients qui ont loué dans une autre ville que celle dans laquelle ils vivent ? Afficher l’identifiant du client, son nom, son prénom, les noms de la ville de résidence et de la ville de location. (1198 lignes distinctes)

TipSolution
WITH 
c_home AS
   (SELECT customer_id, last_name, first_name, city_id
   FROM customer JOIN address USING (address_id)
   ),
c_rental AS
   (SELECT r.customer_id, a.city_id
   FROM rental r JOIN inventory i USING (inventory_id)
        JOIN store s ON i.store_id = s.store_id
        JOIN address a ON s.address_id = a.address_id
   )
SELECT DISTINCT ch.customer_id, ch.last_name, ch.first_name, ci1.city AS city_home, ci2.city AS city_rental 
FROM city ci1 JOIN c_home ch ON ci1.city_id = ch.city_id
     JOIN c_rental cr USING (customer_id)
     JOIN city ci2 ON cr.city_id = ci2.city_id
WHERE ch.city_id <> cr.city_id
ORDER BY ch.last_name, ch.first_name;
NoteQuestion

Quel est l’acteur présent dans le plus de films ? (GINA DEGENERES, 42 films)

TipSolution
WITH actor_film AS
   (SELECT actor_id, first_name, last_name, COUNT(DISTINCT film_id) nbrfilms
    FROM actor JOIN film_actor USING (actor_id)
    GROUP BY actor_id
   )
SELECT *
FROM actor_film
WHERE nbrfilms = (SELECT MAX(nbrfilms) FROM actor_film) ;
NoteQuestion

Quel est le film (id et nom) qui a rapporté le plus d’argent ? (879, TELEGRAPH VOYAGE)

TipSolution
WITH film_amount AS
   (SELECT film_id , SUM(amount) AS dollar
   FROM inventory 
      JOIN rental USING (inventory_id)
      JOIN payment USING (rental_id)
   GROUP BY film_id
   )
SELECT film_id , title, dollar
FROM  film_amount JOIN film USING (film_id)
WHERE dollar >= ALL (SELECT dollar FROM film_amount) ;